﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Text;
using JinianNet.JNCms.Data;

namespace JinianNet.JNCms.SQLiteDAL
{
    public class Users : BaseDAL, IUsers
    {

        public int Update(Models.Users model)
        {
            SQLiteParameter[] parameters = {
					new SQLiteParameter("@GroupID", DbType.Int32,8),
					new SQLiteParameter("@UserType", DbType.Int32,8),
					new SQLiteParameter("@UserName", DbType.String,10),
					new SQLiteParameter("@Account", DbType.String,50),
					new SQLiteParameter("@NickName", DbType.String,50),
					new SQLiteParameter("@UserPassword", DbType.String,50),
					new SQLiteParameter("@Question", DbType.String,50),
					new SQLiteParameter("@Answer", DbType.String,50),
					new SQLiteParameter("@Email", DbType.String,50),
					new SQLiteParameter("@Sex", DbType.Boolean,1),
					new SQLiteParameter("@RegTime", DbType.DateTime),
					new SQLiteParameter("@JoinTime", DbType.DateTime),
					new SQLiteParameter("@LoginCount", DbType.Int32,8),
					new SQLiteParameter("@LastLoginTime", DbType.DateTime),
					new SQLiteParameter("@LastPresentTime", DbType.DateTime),
					new SQLiteParameter("@LastLoginIP", DbType.String,10),
					new SQLiteParameter("@LastPasswordChangedTime", DbType.DateTime),
					new SQLiteParameter("@LastLockoutTime", DbType.DateTime),
					new SQLiteParameter("@FailedPasswordAttemptCount", DbType.Int32,4),
					new SQLiteParameter("@LastFailedPasswordAttempTime", DbType.DateTime),
					new SQLiteParameter("@FailedPasswordAnswerAttempCount", DbType.Int32,4),
					new SQLiteParameter("@LastFailedPasswordAnswerAttempTime", DbType.DateTime),
					new SQLiteParameter("@Status", DbType.Int32,4),
					new SQLiteParameter("@EnableResetPassword", DbType.Boolean,1),
					new SQLiteParameter("@UserFace", DbType.String,100),
					new SQLiteParameter("@FaceWidth", DbType.Int32,4),
					new SQLiteParameter("@FaceHeight", DbType.Int32,4),
					new SQLiteParameter("@Sign", DbType.String,2000),
					new SQLiteParameter("@PrivacySetting", DbType.Int32,4),
					new SQLiteParameter("@Balance", DbType.Decimal,8),
					new SQLiteParameter("@UserPoint", DbType.Int32,8),
					new SQLiteParameter("@UserExp", DbType.Int32,8),
					new SQLiteParameter("@EndTime", DbType.DateTime),
					new SQLiteParameter("@IsInheritGroupRole", DbType.Boolean,1),
					new SQLiteParameter("@UserPermission", DbType.String,1000),
					new SQLiteParameter("@GetPasswordSid", DbType.String,100),
					new SQLiteParameter("@GetPasswordTime", DbType.DateTime),
					new SQLiteParameter("@HonorType", DbType.Int32,4),
					new SQLiteParameter("@IDCard", DbType.String,20),
					new SQLiteParameter("@CurrentLoginTime", DbType.DateTime),
					new SQLiteParameter("@CurrentLoginIP", DbType.String,20),
					new SQLiteParameter("@IsAdministrator", DbType.Boolean,1),
					new SQLiteParameter("@ID", DbType.Int32,8)};
            parameters[0].Value = model.GroupID;
            parameters[1].Value = model.UserType;
            parameters[2].Value = model.UserName;
            parameters[3].Value = model.Account;
            parameters[4].Value = model.NickName;
            parameters[5].Value = model.UserPassword;
            parameters[6].Value = model.Question;
            parameters[7].Value = model.Answer;
            parameters[8].Value = model.Email;
            parameters[9].Value = model.Sex;
            parameters[10].Value = model.RegTime;
            parameters[11].Value = model.JoinTime;
            parameters[12].Value = model.LoginCount;
            parameters[13].Value = model.LastLoginTime;
            parameters[14].Value = model.LastPresentTime;
            parameters[15].Value = model.LastLoginIP;
            parameters[16].Value = model.LastPasswordChangedTime;
            parameters[17].Value = model.LastLockoutTime;
            parameters[18].Value = model.FailedPasswordAttemptCount;
            parameters[19].Value = model.LastFailedPasswordAttempTime;
            parameters[20].Value = model.FailedPasswordAnswerAttempCount;
            parameters[21].Value = model.LastFailedPasswordAnswerAttempTime;
            parameters[22].Value = model.Status;
            parameters[23].Value = model.EnableResetPassword;
            parameters[24].Value = model.UserFace;
            parameters[25].Value = model.FaceWidth;
            parameters[26].Value = model.FaceHeight;
            parameters[27].Value = model.Sign;
            parameters[28].Value = model.PrivacySetting;
            parameters[29].Value = model.Balance;
            parameters[30].Value = model.UserPoint;
            parameters[31].Value = model.UserExp;
            parameters[32].Value = model.EndTime;
            parameters[33].Value = model.IsInheritGroupRole;
            parameters[34].Value = model.UserPermission;
            parameters[35].Value = model.GetPasswordSid;
            parameters[36].Value = model.GetPasswordTime;
            parameters[37].Value = model.HonorType;
            parameters[38].Value = model.IDCard;
            parameters[39].Value = model.CurrentLoginTime;
            parameters[40].Value = model.CurrentLoginIP;
            parameters[41].Value = model.IsAdministrator;
            parameters[42].Value = model.ID;
            return Db.Helper.ExecuteNonQuery(CommandType.Text, "update JNCms_Users set [GroupID]=@GroupID,[UserType]=@UserType,[UserName]=@UserName,Account]=@Account,[NickName]=@NickName,[UserPassword]=@UserPassword,[Question]=@Question,[Answer]=@Answer,[Email]=@Email,[Sex]=@Sex,[RegTime]=@RegTime,[JoinTime]=@JoinTime,[LoginCount]=@LoginCount,[LastLoginTime]=@LastLoginTime,[LastPresentTime]=@LastPresentTime,[LastLoginIP]=@LastLoginIP,[LastPasswordChangedTime]=@LastPasswordChangedTime,[LastLockoutTime]=@LastLockoutTime,[FailedPasswordAttemptCount]=@FailedPasswordAttemptCount,[LastFailedPasswordAttempTime]=@LastFailedPasswordAttempTime,[FailedPasswordAnswerAttempCount]=@FailedPasswordAnswerAttempCount,[LastFailedPasswordAnswerAttempTime]=@LastFailedPasswordAnswerAttempTime,[Status]=@Status,[EnableResetPassword]=@EnableResetPassword,[UserFace]=@UserFace,[FaceWidth]=@FaceWidth,[FaceHeight]=@FaceHeight,[Sign]=@Sign,[PrivacySetting]=@PrivacySetting,[Balance]=@Balance,[UserPoint]=@UserPoint,[UserExp]=@UserExp,[EndTime]=@EndTime,[IsInheritGroupRole]=@IsInheritGroupRole,[UserPermission]=@UserPermission,[GetPasswordSid]=@GetPasswordSid,[GetPasswordTime]=@GetPasswordTime,[HonorType]=@HonorType,[IDCard]=@IDCard,[CurrentLoginTime]=@CurrentLoginTime,[CurrentLoginIP]=@CurrentLoginIP,[IsAdministrator]=@IsAdministrator where [ID]=@ID", parameters);

			
        }

        public int Delete(int id)
        {
            SQLiteParameter[] parameters = {
					new SQLiteParameter("@ID", DbType.Int32,4)};
            parameters[0].Value = id;

            return Db.Helper.ExecuteNonQuery(CommandType.Text, "delete from JNCms_Users where [ID]=@ID", parameters);
        }

        public List<Models.Users> GetList(int id, int pageIndex, int pageSize)
        {
            SQLiteParameter[] parameters = {
					new SQLiteParameter("@GroupID", DbType.Int32,4)};
            parameters[0].Value = id;
            List<Models.Users> list = new List<Models.Users>();
            using (IDataReader rd = Db.Helper.ExecuteReaderPage(db.Helper.ConnectionString, "*", "JNCms_Users where [GroupID]=@GroupID", "ID", null, "Order by ID desc", pageIndex, pageSize, parameters))
            {

                while (rd.Read())
                {
                    list.Add(Db.Entity.PopulateEntity<Models.Users>(rd));
                }
                //list = Db.Entity.PopulateEntityCollection <Models.Article>(rd);
            }

            return list;
        }

        public List<Models.Users> GetList(int id, string key, string keyType, int pageIndex, int pageSize)
        {
            List<SQLiteParameter> parameters = new List<SQLiteParameter>();
            List<string> whereValue = new List<string>();
            
            if (id > 0)
            {
                whereValue.Add("[GroupID]=@GroupID");
                parameters.Add(new SQLiteParameter("@GroupID", DbType.Int32, 4));
                parameters[parameters.Count-1].Value = id;
            }

            if (!string.IsNullOrEmpty(keyType))
            {
                if (!string.IsNullOrEmpty(key))
                {
                    whereValue.Add(string.Concat("[", key, "] like @", key));
                    parameters.Add(new SQLiteParameter(string.Concat("@", key), DbType.String));
                    parameters[parameters.Count - 1].Value = string.Concat("%", key, "%");
                }
            }

            string whereSql;
            if (parameters.Count > 0)
            {
                whereSql = string.Concat("JNCms_Users where ", string.Join(" and ", whereValue.ToArray()));
            }
            else
            {
                whereSql = "JNCms_Users";
            }
         
            List<Models.Users> list = new List<Models.Users>();
            using (IDataReader rd = Db.Helper.ExecuteReaderPage(db.Helper.ConnectionString, "*", "JNCms_Users where [GroupID]=@GroupID", "ID", null, "Order by ID desc", pageIndex, pageSize, parameters.ToArray()))
            {

                while (rd.Read())
                {
                    list.Add(Db.Entity.PopulateEntity<Models.Users>(rd));
                }
                //list = Db.Entity.PopulateEntityCollection <Models.Article>(rd);
            }

            return list;
        }

        public Models.Users GetItem(string item)
        {
            using (IDataReader rd = Db.Helper.ExecuteReader(CommandType.Text, "select * from [JNCms_Users] where [Account]=@Account limit 1", new SQLiteParameter("@Account", item)))
            {
                if (rd.Read())
                {
                    return Db.Entity.PopulateEntity<Models.Users>(rd);
                }
            }
            return null;
        }

        public Models.Users GetItem(int id)
        {
            using (IDataReader rd = Db.Helper.ExecuteReader(CommandType.Text, "select * from [JNCms_Users] where [ID]=@ID limit 1", new SQLiteParameter("@ID", id)))
            {
                if (rd.Read())
                {
                    return Db.Entity.PopulateEntity<Models.Users>(rd);
                }
            }
            return null;
        }

        public int Insert(Models.Users model)
        {
            if (model.ID <= 0)
            {
                model.ID = Convert.ToInt32(Db.Helper.ExecuteScalar("select max(ID) from JNCms_Users")) + 1;
            }
            SQLiteParameter[] parameters = {
					new SQLiteParameter("@ID", DbType.Int32,8),
					new SQLiteParameter("@GroupID", DbType.Int32,8),
					new SQLiteParameter("@UserType", DbType.Int32,8),
					new SQLiteParameter("@UserName", DbType.String,10),
					new SQLiteParameter("@Account", DbType.String,50),
					new SQLiteParameter("@NickName", DbType.String,50),
					new SQLiteParameter("@UserPassword", DbType.String,50),
					new SQLiteParameter("@Question", DbType.String,50),
					new SQLiteParameter("@Answer", DbType.String,50),
					new SQLiteParameter("@Email", DbType.String,50),
					new SQLiteParameter("@Sex", DbType.Boolean,1),
					new SQLiteParameter("@RegTime", DbType.DateTime),
					new SQLiteParameter("@JoinTime", DbType.DateTime),
					new SQLiteParameter("@LoginCount", DbType.Int32,8),
					new SQLiteParameter("@LastLoginTime", DbType.DateTime),
					new SQLiteParameter("@LastPresentTime", DbType.DateTime),
					new SQLiteParameter("@LastLoginIP", DbType.String,10),
					new SQLiteParameter("@LastPasswordChangedTime", DbType.DateTime),
					new SQLiteParameter("@LastLockoutTime", DbType.DateTime),
					new SQLiteParameter("@FailedPasswordAttemptCount", DbType.Int32,4),
					new SQLiteParameter("@LastFailedPasswordAttempTime", DbType.DateTime),
					new SQLiteParameter("@FailedPasswordAnswerAttempCount", DbType.Int32,4),
					new SQLiteParameter("@LastFailedPasswordAnswerAttempTime", DbType.DateTime),
					new SQLiteParameter("@Status", DbType.Int32,4),
					new SQLiteParameter("@EnableResetPassword", DbType.Boolean,1),
					new SQLiteParameter("@UserFace", DbType.String,100),
					new SQLiteParameter("@FaceWidth", DbType.Int32,4),
					new SQLiteParameter("@FaceHeight", DbType.Int32,4),
					new SQLiteParameter("@Sign", DbType.String,2000),
					new SQLiteParameter("@PrivacySetting", DbType.Int32,4),
					new SQLiteParameter("@Balance", DbType.Decimal,8),
					new SQLiteParameter("@UserPoint", DbType.Int32,8),
					new SQLiteParameter("@UserExp", DbType.Int32,8),
					new SQLiteParameter("@EndTime", DbType.DateTime),
					new SQLiteParameter("@IsInheritGroupRole", DbType.Boolean,1),
					new SQLiteParameter("@UserPermission", DbType.String,1000),
					new SQLiteParameter("@GetPasswordSid", DbType.String,100),
					new SQLiteParameter("@GetPasswordTime", DbType.DateTime),
					new SQLiteParameter("@HonorType", DbType.Int32,4),
					new SQLiteParameter("@IDCard", DbType.String,20),
					new SQLiteParameter("@CurrentLoginTime", DbType.DateTime),
                    new SQLiteParameter("@IsAdministrator", DbType.Boolean,1),
					new SQLiteParameter("@CurrentLoginIP", DbType.String,20)};
            parameters[0].Value = model.ID;
            parameters[1].Value = model.GroupID;
            parameters[2].Value = model.UserType;
            parameters[3].Value = model.UserName;
            parameters[4].Value = model.Account;
            parameters[5].Value = model.NickName;
            parameters[6].Value = model.UserPassword;
            parameters[7].Value = model.Question;
            parameters[8].Value = model.Answer;
            parameters[9].Value = model.Email;
            parameters[10].Value = model.Sex;
            parameters[11].Value = model.RegTime;
            parameters[12].Value = model.JoinTime;
            parameters[13].Value = model.LoginCount;
            parameters[14].Value = model.LastLoginTime;
            parameters[15].Value = model.LastPresentTime;
            parameters[16].Value = model.LastLoginIP;
            parameters[17].Value = model.LastPasswordChangedTime;
            parameters[18].Value = model.LastLockoutTime;
            parameters[19].Value = model.FailedPasswordAttemptCount;
            parameters[20].Value = model.LastFailedPasswordAttempTime;
            parameters[21].Value = model.FailedPasswordAnswerAttempCount;
            parameters[22].Value = model.LastFailedPasswordAnswerAttempTime;
            parameters[23].Value = model.Status;
            parameters[24].Value = model.EnableResetPassword;
            parameters[25].Value = model.UserFace;
            parameters[26].Value = model.FaceWidth;
            parameters[27].Value = model.FaceHeight;
            parameters[28].Value = model.Sign;
            parameters[29].Value = model.PrivacySetting;
            parameters[30].Value = model.Balance;
            parameters[31].Value = model.UserPoint;
            parameters[32].Value = model.UserExp;
            parameters[33].Value = model.EndTime;
            parameters[34].Value = model.IsInheritGroupRole;
            parameters[35].Value = model.UserPermission;
            parameters[36].Value = model.GetPasswordSid;
            parameters[37].Value = model.GetPasswordTime;
            parameters[38].Value = model.HonorType;
            parameters[39].Value = model.IDCard;
            parameters[40].Value = model.CurrentLoginTime;
            parameters[41].Value = model.IsAdministrator;
            parameters[42].Value = model.CurrentLoginIP;

            return Db.Helper.ExecuteNonQuery(CommandType.Text, "insert into JNCms_Users([ID],[GroupID],[UserType],[UserName],[Account],[NickName],[UserPassword],[Question],[Answer],[Email],[Sex],[RegTime],[JoinTime],[LoginCount],[LastLoginTime],[LastPresentTime],[LastLoginIP],[LastPasswordChangedTime],[LastLockoutTime],[FailedPasswordAttemptCount],[LastFailedPasswordAttempTime],[FailedPasswordAnswerAttempCount],[LastFailedPasswordAnswerAttempTime],[Status],[EnableResetPassword],[UserFace],[FaceWidth],[FaceHeight],[Sign],[PrivacySetting],[Balance],[UserPoint],[UserExp],[EndTime],[IsInheritGroupRole],[UserPermission],[GetPasswordSid],[GetPasswordTime],[HonorType],[IDCard],[CurrentLoginTime],[CurrentLoginIP],[IsAdministrator]) values (@ID,@GroupID,@UserType,@UserName,@Account,@NickName,@UserPassword,@Question,@Answer,@Email,@Sex,@RegTime,@JoinTime,@LoginCount,@LastLoginTime,@LastPresentTime,@LastLoginIP,@LastPasswordChangedTime,@LastLockoutTime,@FailedPasswordAttemptCount,@LastFailedPasswordAttempTime,@FailedPasswordAnswerAttempCount,@LastFailedPasswordAnswerAttempTime,@Status,@EnableResetPassword,@UserFace,@FaceWidth,@FaceHeight,@Sign,@PrivacySetting,@Balance,@UserPoint,@UserExp,@EndTime,@IsInheritGroupRole,@UserPermission,@GetPasswordSid,@GetPasswordTime,@HonorType,@IDCard,@CurrentLoginTime,@CurrentLoginIP,@IsAdministrator)", parameters);
        }

        public void CreatePasswordSign(string account,string sign, DateTime date)
        {
            SQLiteParameter[] parameters = {
					new SQLiteParameter("@GetPasswordSid", DbType.String,100),
					new SQLiteParameter("@GetPasswordTime", DbType.DateTime),
					new SQLiteParameter("@Account", DbType.String,50)};
            parameters[0].Value = sign;
            parameters[1].Value = date;
            parameters[2].Value =account;
            
            Db.Helper.ExecuteNonQuery(CommandType.Text, "update JNCms_Users set [GetPasswordSid]=@GetPasswordSid,[GetPasswordTime]=@GetPasswordTime where [Account]=@Account", parameters);
        }

        public void LoginSuccess(string account, string ip, DateTime date)
        {
            SQLiteParameter[] parameters = {
					new SQLiteParameter("@CurrentLoginTime", DbType.DateTime),
					new SQLiteParameter("@CurrentLoginIP", DbType.String,20),
					new SQLiteParameter("@Account", DbType.String,50)};
            parameters[0].Value = date;
            parameters[1].Value = ip;
            parameters[2].Value = account;

            Db.Helper.ExecuteNonQuery(CommandType.Text, "update JNCms_Users set [FailedPasswordAttemptCount]=0,[LoginCount]=[LoginCount]+1,LastLoginTime=[CurrentLoginTime],LastLoginIP=[CurrentLoginIP],CurrentLoginTime=@CurrentLoginTime,CurrentLoginIP=@CurrentLoginIP where [Account]=@Account", parameters);
        }

        public void LoginFail(string account, string ip, DateTime date)
        {
            SQLiteParameter[] parameters = {
					new SQLiteParameter("@LastFailedPasswordAttempTime", DbType.DateTime),
					new SQLiteParameter("@Account", DbType.String,50)};
            parameters[0].Value = date;
            parameters[1].Value = account;

            Db.Helper.ExecuteNonQuery(CommandType.Text, "update JNCms_Users set [FailedPasswordAttemptCount]=[FailedPasswordAttemptCount]+1,[LastFailedPasswordAttempTime]=@LastFailedPasswordAttempTime where [Account]=@Account", parameters);
        }

        public int UpdateInformation(Models.Users model)
        {
            throw new NotImplementedException();
        }

        public int UpdatePassword(string account, string password)
        {
            SQLiteParameter[] parameters = {
					new SQLiteParameter("@LastPasswordChangedTime", DbType.DateTime),
                    new SQLiteParameter("@UserPassword", DbType.String,50),
					new SQLiteParameter("@Account", DbType.String,50)};
            parameters[0].Value = DateTime.Now;
            parameters[1].Value = password;
            parameters[2].Value = account;

            return Db.Helper.ExecuteNonQuery(CommandType.Text, "update JNCms_Users set [LastPasswordChangedTime]=[LastPasswordChangedTime],[UserPassword]=@UserPassword where [Account]=@Account", parameters);
        }

        public int UpdateSafeQuestion(string account, string question, string answer)
        {
            SQLiteParameter[] parameters = {
                    new SQLiteParameter("@Question", DbType.String,50),
					new SQLiteParameter("@Answer", DbType.String,50),
					new SQLiteParameter("@Account", DbType.String,50)};
            parameters[0].Value = question;
            parameters[1].Value = answer;
            parameters[2].Value = account;

            return Db.Helper.ExecuteNonQuery(CommandType.Text, "update JNCms_Users set [Question]=@Question,[Answer]=@Answer where [Account]=@Account", parameters);
        }

        public int UpdateIDCard(string account, string card)
        {
            SQLiteParameter[] parameters = {
					new SQLiteParameter("@IDCard", DbType.String,20),
					new SQLiteParameter("@Account", DbType.String,50)};
            parameters[0].Value = card;
            parameters[1].Value = account;

            return Db.Helper.ExecuteNonQuery(CommandType.Text, "update JNCms_Users set [IDCard]=@IDCard where [Account]=@Account", parameters);
        }
    }
}
